package com.school.sports.util;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.time.LocalDateTime;

/**
 * 异常清理工具类
 * 用于清理系统中的历史异常记录
 *
 * @author 系统开发团队
 * @since 2025-10-14
 */
@Slf4j
@Component
@Order(3)
public class ExceptionCleanupUtil implements CommandLineRunner {

    @Autowired
    private DataSource dataSource;

    @Override
    public void run(String... args) throws Exception {
        log.info("开始检查和清理历史异常记录...");

        // 清理待处理的异常记录
        cleanupPendingExceptions();

        log.info("历史异常记录清理完成");
    }

    /**
     * 清理待处理的异常记录
     */
    private void cleanupPendingExceptions() {
        try (Connection connection = dataSource.getConnection()) {

            // 1. 统计当前异常记录数量
            String countSql = "SELECT COUNT(*) as total FROM exception_record WHERE deleted = 0";
            try (PreparedStatement countStmt = connection.prepareStatement(countSql);
                 ResultSet countRs = countStmt.executeQuery()) {

                if (countRs.next()) {
                    int total = countRs.getInt("total");
                    log.info("当前数据库中共有 {} 条异常记录", total);
                }
            }

            // 2. 清理超过7天的待处理异常记录
            String cleanupOldSql = "UPDATE exception_record SET handle_status = 3, updated_at = ? " +
                                  "WHERE deleted = 0 AND handle_status = 0 AND occur_time < ?";
            LocalDateTime sevenDaysAgo = LocalDateTime.now().minusDays(7);

            try (PreparedStatement cleanupStmt = connection.prepareStatement(cleanupOldSql)) {
                cleanupStmt.setObject(1, LocalDateTime.now());
                cleanupStmt.setObject(2, sevenDaysAgo);

                int affectedRows = cleanupStmt.executeUpdate();
                if (affectedRows > 0) {
                    log.info("已将 {} 条超过7天的待处理异常标记为已忽略", affectedRows);
                }
            }

            // 3. 批量处理剩余的待处理异常（标记为已忽略）
            String ignorePendingSql = "UPDATE exception_record SET handle_status = 3, " +
                                     "handler_name = '系统自动处理', handle_time = ?, updated_at = ? " +
                                     "WHERE deleted = 0 AND handle_status = 0";

            try (PreparedStatement ignoreStmt = connection.prepareStatement(ignorePendingSql)) {
                LocalDateTime now = LocalDateTime.now();
                ignoreStmt.setObject(1, now);
                ignoreStmt.setObject(2, now);

                int affectedRows = ignoreStmt.executeUpdate();
                if (affectedRows > 0) {
                    log.info("已将 {} 条待处理异常标记为已忽略", affectedRows);

                    // 4. 记录清理操作的日志
                    String logSql = "INSERT INTO exception_record (exception_type, exception_desc, occur_time, " +
                                  "occur_location, exception_level, handle_status, handler_name, " +
                                  "handle_time, solution, operator_name, created_at, updated_at) " +
                                  "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

                    try (PreparedStatement logStmt = connection.prepareStatement(logSql)) {
                        logStmt.setString(1, "系统维护");
                        logStmt.setString(2, "系统启动时自动清理历史异常记录，共处理 " + affectedRows + " 条待处理异常");
                        logStmt.setObject(3, now);
                        logStmt.setString(4, "ExceptionCleanupUtil");
                        logStmt.setInt(5, 3); // 轻微异常
                        logStmt.setInt(6, 3); // 已忽略
                        logStmt.setString(7, "系统自动处理");
                        logStmt.setObject(8, now);
                        logStmt.setString(9, "系统启动时自动清理，避免历史异常影响系统正常运行");
                        logStmt.setString(10, "系统");
                        logStmt.setObject(11, now);
                        logStmt.setObject(12, now);

                        logStmt.executeUpdate();
                        log.info("已记录异常清理操作日志");
                    }
                }
            }

            // 5. 验证清理结果
            String verifySql = "SELECT COUNT(*) as pending FROM exception_record WHERE deleted = 0 AND handle_status = 0";
            try (PreparedStatement verifyStmt = connection.prepareStatement(verifySql);
                 ResultSet verifyRs = verifyStmt.executeQuery()) {

                if (verifyRs.next()) {
                    int pendingCount = verifyRs.getInt("pending");
                    if (pendingCount == 0) {
                        log.info("✅ 所有待处理异常已成功清理");
                    } else {
                        log.warn("⚠️ 仍有 {} 条待处理异常未能清理", pendingCount);
                    }
                }
            }

        } catch (Exception e) {
            log.error("清理历史异常记录失败：{}", e.getMessage(), e);
        }
    }
}